﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop.Word;
using System.Reflection;
using System.IO;
using System.Drawing;

using FbKPI.Model;
namespace FbKPI.Controller
{
    public class ReportController : FbKPI.Controller.dbConnection.ConnectionClass
    {
        public IList<AnnualReport> GetAnnualReportData(int year, Employee User)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetAnnualReport";
            command.Parameters.Add("@YEAR", SqlDbType.VarChar).Value = year;
            command.Parameters.Add("@USERID", SqlDbType.VarChar).Value = User.ID;

            DataSet ds = GetTable(command);

            IList<AnnualReport> result = new List<AnnualReport>();

            if (ds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    AnnualReport d = new AnnualReport();
                    d.Month = ds.Tables[0].Rows[i]["Month"].ToString();
                    d.Description = ds.Tables[0].Rows[i]["KPIDescription"].ToString();
                    d.UoMMax = ds.Tables[0].Rows[i]["UoMMax"].ToString();
                    d.UoMMin = ds.Tables[0].Rows[i]["UoMMin"].ToString();
                    d.Realization = ds.Tables[0].Rows[i]["Realization"].ToString();
                    d.Score = ds.Tables[0].Rows[i]["Score"].ToString();
                    d.StatusUser = ds.Tables[0].Rows[i]["StatusByUser"].ToString();
                    d.StatusVerificator = ds.Tables[0].Rows[i]["StatusByVerificator"].ToString();
                    result.Add(d);
                }
            }

            return result;

        }

        public string GenerateHandoverKPI(int year, Employee user, string logicpath, string outputpath, KPIAssignDetail KPIs)
        {
            Microsoft.Office.Interop.Word.Application apps = new Microsoft.Office.Interop.Word.Application();
            Document doc = new Document();
            
            object Miss = Type.Missing;
            object path = System.Web.HttpContext.Current.Server.MapPath(logicpath);
            object serveroutputpath = System.Web.HttpContext.Current.Server.MapPath(outputpath);

            doc = apps.Documents.Open(ref path, ref Miss, ref Miss, ref Miss, ref Miss, ref Miss, ref Miss, ref Miss, ref Miss, ref Miss, ref Miss, ref Miss, ref Miss, ref Miss, ref Miss);

            doc.Activate();

            double Achievement = 0;
            string AllText = string.Empty;

            foreach (Microsoft.Office.Interop.Word.Range ranges in doc.StoryRanges)
            {
                if (Achievement == 0)
                {
                    IList<KPIAchievement> achieves = new KPIAchievementController().GetKPIAchievementTable(year, KPIs.KPIAssign == null ? 0 : KPIs.KPIAssign.ID);
                    if (achieves != null && achieves.Count > 0)
                    {
                        int jmlData = 0;

                        foreach (KPIAchievement y in achieves)
                        {
                            if (y != null)
                            {
                                if (y.KPIAssign.UoM.Format == UoMFormat.Numeric)
                                {
                                    Achievement = Achievement + (y == null ? 0 : (y.Score == "" ? 0 : Convert.ToDouble(y.Score)));
                                    jmlData = 12;
                                }
                                else if (y.KPIAssign.UoM.Format == UoMFormat.Month)
                                {
                                    Achievement = y.Score == "" ? 0 : Convert.ToDouble(y.Score);
                                    jmlData = 1;
                                }
                                else
                                {
                                    Achievement = y.Score == "" ? 0 : Convert.ToDouble(y.Score);
                                    jmlData = achieves.Where(s => s != null).Count();
                                }
                            }
                        }

                        Achievement = Achievement / (jmlData == 0 ? 1 : jmlData);
                    }
                }

                //replace marked object
                ranges.Find.Text = "<%#KPIName%>";
                ranges.Find.Replacement.Text = KPIs.KPI.Description;
                ranges.Find.Wrap = WdFindWrap.wdFindContinue;
                object replaceAll = WdReplace.wdReplaceAll;
                ranges.Find.Execute(ref Miss, ref Miss, ref Miss,
                        ref Miss, ref Miss, ref Miss, ref Miss,
                        ref Miss, ref Miss, ref Miss, ref replaceAll,
                        ref Miss, ref Miss, ref Miss, ref Miss);

                ranges.Find.Text = "<%#Date%>";
                ranges.Find.Replacement.Text = DateTime.Now.Date.ToString("dd/MM/yyyy");
                ranges.Find.Wrap = WdFindWrap.wdFindContinue;
                replaceAll = WdReplace.wdReplaceAll;
                ranges.Find.Execute(ref Miss, ref Miss, ref Miss,
                        ref Miss, ref Miss, ref Miss, ref Miss,
                        ref Miss, ref Miss, ref Miss, ref replaceAll,
                        ref Miss, ref Miss, ref Miss, ref Miss);

                ranges.Find.Text = "<%#PIC%>";
                ranges.Find.Replacement.Text = KPIs.Employee.FirstName + " " + KPIs.Employee.LastName;
                ranges.Find.Wrap = WdFindWrap.wdFindContinue;
                replaceAll = WdReplace.wdReplaceAll;
                ranges.Find.Execute(ref Miss, ref Miss, ref Miss,
                        ref Miss, ref Miss, ref Miss, ref Miss,
                        ref Miss, ref Miss, ref Miss, ref replaceAll,
                        ref Miss, ref Miss, ref Miss, ref Miss);

                ranges.Find.Text = "<%#Superior%>";
                ranges.Find.Replacement.Text = KPIs.KPIAssign.Verifier.FirstName + " " + KPIs.KPIAssign.Verifier.LastName;
                ranges.Find.Wrap = WdFindWrap.wdFindContinue;
                replaceAll = WdReplace.wdReplaceAll;
                ranges.Find.Execute(ref Miss, ref Miss, ref Miss,
                        ref Miss, ref Miss, ref Miss, ref Miss,
                        ref Miss, ref Miss, ref Miss, ref replaceAll,
                        ref Miss, ref Miss, ref Miss, ref Miss);

                ranges.Find.Text = "<%#Objectives%>";
                ranges.Find.Replacement.Text = KPIs.KPI.KPIStrategicObjective;
                ranges.Find.Wrap = WdFindWrap.wdFindContinue;
                replaceAll = WdReplace.wdReplaceAll;
                ranges.Find.Execute(ref Miss, ref Miss, ref Miss,
                        ref Miss, ref Miss, ref Miss, ref Miss,
                        ref Miss, ref Miss, ref Miss, ref replaceAll,
                        ref Miss, ref Miss, ref Miss, ref Miss);

                ranges.Find.Text = "<%#Description%>";
                ranges.Find.Replacement.Text = KPIs.KPI.TargetDescription;
                ranges.Find.Wrap = WdFindWrap.wdFindContinue;
                replaceAll = WdReplace.wdReplaceAll;
                ranges.Find.Execute(ref Miss, ref Miss, ref Miss,
                        ref Miss, ref Miss, ref Miss, ref Miss,
                        ref Miss, ref Miss, ref Miss, ref replaceAll,
                        ref Miss, ref Miss, ref Miss, ref Miss);

                ranges.Find.Text = "<%#Weight%>";
                ranges.Find.Replacement.Text = KPIs.Weight.ToString() + "%";
                ranges.Find.Wrap = WdFindWrap.wdFindContinue;
                replaceAll = WdReplace.wdReplaceAll;
                ranges.Find.Execute(ref Miss, ref Miss, ref Miss,
                        ref Miss, ref Miss, ref Miss, ref Miss,
                        ref Miss, ref Miss, ref Miss, ref replaceAll,
                        ref Miss, ref Miss, ref Miss, ref Miss);

                ranges.Find.Text = "<%#Realization%>";
                ranges.Find.Replacement.Text = Achievement.ToString();
                ranges.Find.Wrap = WdFindWrap.wdFindContinue;
                replaceAll = WdReplace.wdReplaceAll;
                ranges.Find.Execute(ref Miss, ref Miss, ref Miss,
                        ref Miss, ref Miss, ref Miss, ref Miss,
                        ref Miss, ref Miss, ref Miss, ref replaceAll,
                        ref Miss, ref Miss, ref Miss, ref Miss);
            }

            doc.SaveAs(ref serveroutputpath, ref Miss, ref Miss, ref Miss, ref Miss, ref Miss, ref Miss, ref Miss, ref Miss, ref Miss, ref Miss, ref Miss, ref Miss, ref Miss, ref Miss, ref Miss);

            ((_Document)doc).Close(ref Miss, ref Miss, ref Miss);
            ((Microsoft.Office.Interop.Word._Application)apps).Quit(ref Miss, ref Miss, ref Miss);
            
            return outputpath;
        }

        public string GenerateManualKPI(int year, Employee user, string logicpath, string outputpath, KPIAssignDetail KPIs)
        {
            Microsoft.Office.Interop.Excel.Application apps = new Microsoft.Office.Interop.Excel.Application();

            var Miss = Missing.Value;
            string path = System.Web.HttpContext.Current.Server.MapPath(logicpath);
            string serveroutputpath = System.Web.HttpContext.Current.Server.MapPath(outputpath);

            Workbook wb = apps.Workbooks.Open(path, Miss, Miss, Miss, Miss, Miss, Miss, Miss, Miss, Miss, Miss, Miss, Miss, Miss, Miss);

            Worksheet ws = (Worksheet)wb.Worksheets[1];

            //ws.Rows.RowHeight = 14;
            //ws.Cells.Interior.Color = Color.White;

            Microsoft.Office.Interop.Excel.Range SheetRange = ws.get_Range("B5:F5", Miss);
            SheetRange.MergeCells = true;
            SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
            SheetRange.set_Value(Miss, KPIs.KPI.TypeKPI.Description.ToString());

            SheetRange = ws.get_Range("B7:F7", Miss);
            SheetRange.MergeCells = true;
            SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
            SheetRange.set_Value(Miss, KPIs.KPI.SubTypeKPI.Description.ToString());

            SheetRange = ws.get_Range("B9:F11", Miss);
            SheetRange.MergeCells = true;
            SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
            SheetRange.set_Value(Miss, KPIs.KPI.SubTypeKPI.Description.ToString());

            SheetRange = ws.get_Range("B17:F27", Miss);
            SheetRange.MergeCells = true;
            SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
            SheetRange.set_Value(Miss, KPIs.KPI.TargetDescription.ToString());

            SheetRange = ws.get_Range("B29:F29", Miss);
            SheetRange.MergeCells = true;
            SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
            SheetRange.set_Value(Miss, "100% = Diselesaikan max / dengan nilai " + KPIs.KPI.UoMDetailMax.Description.ToString());

            SheetRange = ws.get_Range("B30:F30", Miss);
            SheetRange.MergeCells = true;
            SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
            SheetRange.set_Value(Miss, "80% = Diselesaikan max / dengan nilai " + KPIs.KPI.UoMDetailMin.Description.ToString());

            SheetRange = ws.get_Range("B34:F39", Miss);
            SheetRange.MergeCells = true;
            SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
            SheetRange.set_Value(Miss, KPIs.KPI.KPIStrategicObjective.ToString());

            SheetRange = ws.get_Range("B41:F41", Miss);
            SheetRange.MergeCells = true;
            SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
            SheetRange.set_Value(Miss, KPIs.KPI.UoM.Description.ToString());


            IList<KPIAssignDetail> KPIList = new KPIAssignDetailController().GetKPIAssignDetailByKPIAssignID(KPIs.KPIAssign.ID);
            int idx = 0;
            string returnValue = string.Empty;
            if (KPIList.Count > 0)
            {
                foreach (KPIAssignDetail k in KPIList)
                {
                    if (idx == 0)
                    {
                        returnValue = k.Employee.Email.Replace("@fastratabuana.co.id",string.Empty);
                    }
                    else
                    {
                        returnValue += ", " + k.Employee.Email.Replace("@fastratabuana.co.id", string.Empty);
                    }
                    idx++;
                }
            }


            SheetRange = ws.get_Range("B43:F43", Miss);
            SheetRange.MergeCells = true;
            SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
            SheetRange.set_Value(Miss, returnValue);

            SheetRange = ws.get_Range("B45:F45", Miss);
            SheetRange.MergeCells = true;
            SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
            SheetRange.set_Value(Miss, string.Empty);

            SheetRange = ws.get_Range("B47:F47", Miss);
            SheetRange.MergeCells = true;
            SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
            SheetRange.set_Value(Miss, string.Empty);

            SheetRange = ws.get_Range("B49:F49", Miss);
            SheetRange.MergeCells = true;
            SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
            SheetRange.set_Value(Miss, KPIs.KPI.Polarization.ToString());

            SheetRange = ws.get_Range("B51:F51", Miss);
            SheetRange.MergeCells = true;
            SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
            SheetRange.set_Value(Miss, KPIs.KPI.Periode);


            wb.SaveAs(serveroutputpath, Miss, Miss, Miss, Miss, Miss, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, Miss, Miss, Miss, Miss);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(SheetRange);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
            wb.Close(Miss, Miss, Miss);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
            apps.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(apps);

            return outputpath;
        }

        public string GenerateExcel(int year, Employee user, string logicpath, string outputpath)
        {
            Microsoft.Office.Interop.Excel.Application apps = new Microsoft.Office.Interop.Excel.Application();

            var Miss = Missing.Value;
            string path = System.Web.HttpContext.Current.Server.MapPath(logicpath);
            string serveroutputpath = System.Web.HttpContext.Current.Server.MapPath(outputpath);

            Workbook wb = apps.Workbooks.Open( path, Miss, Miss, Miss, Miss, Miss, Miss, Miss, Miss, Miss, Miss, Miss, Miss, Miss, Miss);

            Worksheet ws = (Worksheet)wb.Worksheets[1];

            ws.Rows.RowHeight = 9;
            ws.Cells.Interior.Color = Color.White;

            Microsoft.Office.Interop.Excel.Range SheetRange = ws.get_Range("C4:I4", Miss);
            SheetRange.MergeCells = true;
            SheetRange.set_Value(Miss, "KEY PERFORMANCE INDICATOR " + year.ToString());

            SheetRange = ws.get_Range("C5:I5", Miss);
            SheetRange.MergeCells = true;
            SheetRange.set_Value(Miss, "DIVISION / DEPARTMENT OF " + user.Department.Description);

            SheetRange = ws.get_Range("B6:D6", Miss);
            SheetRange.MergeCells = true;
            SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
            SheetRange.set_Value(Miss, DateTime.Now.Date.ToShortDateString());

            SheetRange = ws.get_Range("B7:D7", Miss);
            SheetRange.MergeCells = true;
            SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
            SheetRange.set_Value(Miss, user.FirstName + " " + user.LastName);

            SheetRange = ws.get_Range("B8:D8", Miss);
            SheetRange.MergeCells = true;
            SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
            SheetRange.set_Value(Miss, user.JoinDate == null ? string.Empty : Convert.ToDateTime(user.JoinDate).ToShortDateString());

            SheetRange = ws.get_Range("G6:L6", Miss);
            SheetRange.MergeCells = true;
            SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
            SheetRange.set_Value(Miss, user.Department.Description);

            SheetRange = ws.get_Range("G7:L7", Miss);
            SheetRange.MergeCells = true;
            SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;

            EmployeeController cl = new EmployeeController();
            Employee obj = cl.GetEmployeeByID(user.ID);
            Employee objSpv = cl.GetEmployeeByID(user.Supervisor);
            string firstName = obj.FirstName;
            string LastName = obj.LastName;
            if (objSpv != null)
            {
                firstName = objSpv.FirstName;
                LastName = objSpv.LastName;
            }
           

            //SheetRange.set_Value(Miss, user.Supervisor == null ? string.Empty : (user.Supervisor.FirstName + " " + user.Supervisor.LastName));
            SheetRange.set_Value(Miss, user.Supervisor == null ? string.Empty : (firstName + " " + LastName));

            SheetRange = ws.get_Range("G8:L8", Miss);
            SheetRange.MergeCells = true;
            SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
            SheetRange.set_Value(Miss, year.ToString());

            
            SheetRange = ws.get_Range("A12", "L300");
            SheetRange.MergeCells = false;
            SheetRange.ClearContents();
            SheetRange.ClearFormats();
            SheetRange.Font.Size = 5;
            SheetRange.Font.Name = "Arial";


            IList<GroupKPI> groups = new GroupKPIController().GetAll();

            var queryGroups = groups.OrderBy(x => x.Sequence);
            groups = queryGroups.ToList();

            int idx = 13;
            string range = string.Empty;
            int Numbering = 1;
            foreach (GroupKPI a in groups)
            {
                double GrandTotal = 0;

                range = "A" + idx.ToString() + ":A" + idx.ToString();
                SheetRange = ws.get_Range(range, Miss);
                SheetRange.Font.Bold = true;
                SheetRange.set_Value(Miss, a.Description);

                idx = idx + 2;

                #region Quantitative

                if (a.Description.Trim().ToLower().Contains("quantitative"))
                {
                    IList<CategoryKPI> Categories = new CategoryKPIController().GetAllCategoryKPIsByGroupKPIID(a.ID);
                    var queryCategories = Categories.OrderBy(x => x.Sequence);
                    Categories = queryCategories.ToList();

                    foreach (CategoryKPI b in Categories)
                    {
                        range = "A" + idx.ToString() + ":A" + (idx + 1).ToString();
                        SheetRange = ws.get_Range(range, Miss);
                        SheetRange.MergeCells = true;
                        SheetRange.Interior.Color = Color.Yellow;
                        SheetRange.Font.Bold = true;
                        SheetRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                        SheetRange.VerticalAlignment = XlVAlign.xlVAlignCenter;
                        SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                        SheetRange.set_Value(Miss, b.Description);

                        range = "B" + idx.ToString() + ":B" + (idx + 1).ToString();
                        SheetRange = ws.get_Range(range, Miss);
                        SheetRange.MergeCells = true;
                        SheetRange.Interior.Color = Color.Yellow;
                        SheetRange.Font.Bold = true;
                        SheetRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                        SheetRange.VerticalAlignment = XlVAlign.xlVAlignCenter;
                        SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                        SheetRange.set_Value(Miss, "No");

                        range = "C" + idx.ToString() + ":C" + (idx + 1).ToString();
                        SheetRange = ws.get_Range(range, Miss);
                        SheetRange.MergeCells = true;
                        SheetRange.Interior.Color = Color.Yellow;
                        SheetRange.Font.Bold = true;
                        SheetRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                        SheetRange.VerticalAlignment = XlVAlign.xlVAlignCenter;
                        SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                        SheetRange.set_Value(Miss, "KPI");

                        range = "D" + idx.ToString() + ":D" + (idx + 1).ToString();
                        SheetRange = ws.get_Range(range, Miss);
                        SheetRange.MergeCells = true;
                        SheetRange.Interior.Color = Color.Yellow;
                        SheetRange.Font.Bold = true;
                        SheetRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                        SheetRange.VerticalAlignment = XlVAlign.xlVAlignCenter;
                        SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                        SheetRange.set_Value(Miss, "Period");

                        range = "E" + idx.ToString() + ":E" + (idx + 1).ToString();
                        SheetRange = ws.get_Range(range, Miss);
                        SheetRange.MergeCells = true;
                        SheetRange.Interior.Color = Color.Yellow;
                        SheetRange.Font.Bold = true;
                        SheetRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                        SheetRange.VerticalAlignment = XlVAlign.xlVAlignCenter;
                        SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                        SheetRange.set_Value(Miss, "Verificator");

                        range = "F" + idx.ToString() + ":G" + idx.ToString();
                        SheetRange = ws.get_Range(range, Miss);
                        SheetRange.MergeCells = true;
                        SheetRange.Interior.Color = Color.Yellow;
                        SheetRange.Font.Bold = true;
                        SheetRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                        SheetRange.VerticalAlignment = XlVAlign.xlVAlignCenter;
                        SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                        SheetRange.set_Value(Miss, "Target");

                        range = "F" + (idx + 1).ToString() + ":F" + (idx + 1).ToString();
                        SheetRange = ws.get_Range(range, Miss);
                        SheetRange.MergeCells = true;
                        SheetRange.Interior.Color = Color.Yellow;
                        SheetRange.Font.Bold = true;
                        SheetRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                        SheetRange.VerticalAlignment = XlVAlign.xlVAlignCenter;
                        SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                        SheetRange.set_Value(Miss, "Max");

                        range = "G" + (idx + 1).ToString() + ":G" + (idx + 1).ToString();
                        SheetRange = ws.get_Range(range, Miss);
                        SheetRange.MergeCells = true;
                        SheetRange.Interior.Color = Color.Yellow;
                        SheetRange.Font.Bold = true;
                        SheetRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                        SheetRange.VerticalAlignment = XlVAlign.xlVAlignCenter;
                        SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                        SheetRange.set_Value(Miss, "Min");

                        range = "H" + idx.ToString() + ":H" + (idx + 1).ToString();
                        SheetRange = ws.get_Range(range, Miss);
                        SheetRange.MergeCells = true;
                        SheetRange.Interior.Color = Color.Yellow;
                        SheetRange.Font.Bold = true;
                        SheetRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                        SheetRange.VerticalAlignment = XlVAlign.xlVAlignCenter;
                        SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                        SheetRange.set_Value(Miss, "UoM");

                        range = "I" + idx.ToString() + ":I" + (idx + 1).ToString();
                        SheetRange = ws.get_Range(range, Miss);
                        SheetRange.MergeCells = true;
                        SheetRange.Interior.Color = Color.Yellow;
                        SheetRange.Font.Bold = true;
                        SheetRange.WrapText = true;
                        SheetRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                        SheetRange.VerticalAlignment = XlVAlign.xlVAlignCenter;
                        SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                        SheetRange.set_Value(Miss, "Weight (%)");

                        range = "J" + idx.ToString() + ":J" + (idx + 1).ToString();
                        SheetRange = ws.get_Range(range, Miss);
                        SheetRange.MergeCells = true;
                        SheetRange.Interior.Color = Color.Yellow;
                        SheetRange.Font.Bold = true;
                        SheetRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                        SheetRange.VerticalAlignment = XlVAlign.xlVAlignCenter;
                        SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                        SheetRange.set_Value(Miss, "Realisasi");

                        range = "K" + idx.ToString() + ":K" + (idx + 1).ToString();
                        SheetRange = ws.get_Range(range, Miss);
                        SheetRange.MergeCells = true;
                        SheetRange.Interior.Color = Color.Yellow;
                        SheetRange.Font.Bold = true;
                        SheetRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                        SheetRange.VerticalAlignment = XlVAlign.xlVAlignCenter;
                        SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                        SheetRange.set_Value(Miss, "Keterangan");

                        range = "L" + idx.ToString() + ":L" + (idx + 1).ToString();
                        SheetRange = ws.get_Range(range, Miss);
                        SheetRange.MergeCells = true;
                        SheetRange.Interior.Color = Color.Yellow;
                        SheetRange.Font.Bold = true;
                        SheetRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                        SheetRange.VerticalAlignment = XlVAlign.xlVAlignCenter;
                        SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                        SheetRange.set_Value(Miss, "Final Score");

                        IList<TypeKPI> Types = new TypeKPIController().GetAllTypeKPIsByCategoryKPIID(b.ID);
                        var queryTypes = Types.OrderBy(x => x.Sequence);
                        Types = queryTypes.ToList();

                        idx = idx + 2;

                        if (Types != null && Types.Count > 0)
                        {
                           
                            for (int i = 0; i < Types.Count; i++)
                            {
                                range = "A" + idx.ToString() + ":L" + idx.ToString();
                                SheetRange = ws.get_Range(range, Miss);
                                SheetRange.MergeCells = true;
                                SheetRange.Interior.Color = Color.LightGray;
                                SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                                SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                                SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                                SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                                SheetRange.Font.Bold = true;
                                SheetRange.set_Value(Miss, Types[i].Description);

                                idx++;

                                IList<SubTypeKPI> SubTypes = new SubTypeKPIController().GetAllAssignedSubTypeKPIsByTypeKPIID(Types[i].ID, user);
                                var querySubTypes = SubTypes.OrderBy(x => x.Sequence);
                                SubTypes = querySubTypes.ToList();

                                if (SubTypes != null && SubTypes.Count > 0)
                                {
                                  
                                    double TotalWeight = 0;
                                    double TotalRealization = 0;
                                    double TotalFinalScore = 0;

                                    foreach (SubTypeKPI d in SubTypes)
                                    {
                                        range = "A" + idx.ToString() + ":A" + idx.ToString();
                                        SheetRange = ws.get_Range(range, Miss);
                                        SheetRange.MergeCells = false;
                                        SheetRange.Interior.Color = Color.White;
                                        SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                                        SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                                        SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                                        SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                                        SheetRange.Borders[XlBordersIndex.xlInsideVertical].Color = Color.Black;
                                        SheetRange.Font.Bold = false;
                                        SheetRange.set_Value(Miss, d.Description);

                                        IList<KPIAssignDetail> KPIs = new KPIAssignDetailController().GetAllByYearAndUserID(user.ID, year);

                                        IList<KPIAssignDetail> KPIDetailsPerType = KPIs.Where(x => x.KPI.SubTypeKPI.ID == d.ID).ToList<KPIAssignDetail>();

                                        if (KPIDetailsPerType != null && KPIDetailsPerType.Count > 0)
                                        {
                                            foreach (KPIAssignDetail e in KPIDetailsPerType)
                                            {

                                                range = "B" + idx.ToString() + ":B" + idx.ToString();
                                                SheetRange = ws.get_Range(range, Miss);
                                                SheetRange.Interior.Color = Color.White;
                                                SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                                                SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                                                SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                                                SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                                                SheetRange.Font.Bold = false;
                                                SheetRange.set_Value(Miss, Numbering);

                                                range = "C" + idx.ToString() + ":C" + idx.ToString();
                                                SheetRange = ws.get_Range(range, Miss);
                                                SheetRange.Interior.Color = Color.White;
                                                SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                                                SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                                                SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                                                SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                                                SheetRange.Font.Bold = false;
                                                SheetRange.set_Value(Miss, e.KPI.Description.Trim());

                                                range = "D" + idx.ToString() + ":D" + idx.ToString();
                                                SheetRange = ws.get_Range(range, Miss);
                                                SheetRange.Interior.Color = Color.White;
                                                SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                                                SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                                                SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                                                SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                                                SheetRange.Font.Bold = false;
                                                SheetRange.set_Value(Miss, e.KPI.Periode);

                                                range = "E" + idx.ToString() + ":E" + idx.ToString();
                                                SheetRange = ws.get_Range(range, Miss);
                                                SheetRange.Interior.Color = Color.White;
                                                SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                                                SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                                                SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                                                SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                                                SheetRange.Font.Bold = false;
                                                
                                                SheetRange.set_Value(Miss, e.KPIAssign == null ? string.Empty : (e.KPIAssign.Verifier == null ? string.Empty : e.KPIAssign.Verifier.FirstName+" " + e.KPIAssign.Verifier.LastName));

                                                range = "F" + idx.ToString() + ":F" + idx.ToString();
                                                SheetRange = ws.get_Range(range, Miss);
                                                SheetRange.Interior.Color = Color.White;
                                                SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                                                SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                                                SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                                                SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                                                SheetRange.Font.Bold = false;
                                                SheetRange.set_Value(Miss, e.KPIAssign == null ? string.Empty : (e.KPIAssign.UoMDetailMax == null ? string.Empty : e.KPIAssign.UoMDetailMax.Description.ToString()));

                                                range = "G" + idx.ToString() + ":G" + idx.ToString();
                                                SheetRange = ws.get_Range(range, Miss);
                                                SheetRange.Interior.Color = Color.White;
                                                SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                                                SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                                                SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                                                SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                                                SheetRange.Font.Bold = false;
                                                SheetRange.set_Value(Miss, e.KPIAssign == null ? string.Empty : (e.KPIAssign.UoMDetailMin == null ? string.Empty : e.KPIAssign.UoMDetailMin.Description.ToString()));

                                                range = "H" + idx.ToString() + ":H" + idx.ToString();
                                                SheetRange = ws.get_Range(range, Miss);
                                                SheetRange.Interior.Color = Color.White;
                                                SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                                                SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                                                SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                                                SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                                                SheetRange.Font.Bold = false;
                                                SheetRange.set_Value(Miss, e.KPIAssign == null ? string.Empty : (e.KPIAssign.UoM == null ? string.Empty : e.KPIAssign.UoM.Description.ToString()));

                                                range = "I" + idx.ToString() + ":I" + idx.ToString();
                                                SheetRange = ws.get_Range(range, Miss);
                                                SheetRange.Interior.Color = Color.White;
                                                SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                                                SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                                                SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                                                SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                                                SheetRange.Font.Bold = false;
                                                SheetRange.set_Value(Miss, e.KPI.Weight.ToString());
                                                TotalWeight = TotalWeight + e.KPI.Weight;

                                                IList<KPIAchievement> achieves = new KPIAchievementController().GetKPIAchievementTable(year, e.KPIAssign == null ? 0 : e.KPIAssign.ID);

                                                if (achieves != null && achieves.Count > 0)
                                                {
                                                    double Achievement = 0;
                                                    string Notes = string.Empty;

                                                    int jmlData = 0;

                                                    foreach (KPIAchievement y in achieves)
                                                    {
                                                        if (y != null)
                                                        {
                                                            if (y.KPIAssign.UoM.Format == UoMFormat.Numeric)
                                                            {
                                                                Achievement = Achievement + (y == null ? 0 : (y.Score == "" ? 0 : Convert.ToDouble(y.Score)));
                                                                if (y != null && y.Notes != string.Empty) Notes = y.Notes;
                                                                jmlData = 12;
                                                            }
                                                            else if (y.KPIAssign.UoM.Format == UoMFormat.Month)
                                                            {
                                                                Achievement = y.Score == "" ? 0 : Convert.ToDouble(y.Score);
                                                                Notes = y.Notes == "" ? string.Empty : y.Notes;
                                                                jmlData = 1;
                                                            }
                                                            else
                                                            {
                                                                Achievement = y.Score == "" ? 0 : Convert.ToDouble(y.Score);
                                                                Notes = y.Notes == "" ? string.Empty : y.Notes;
                                                                jmlData = achieves.Where(s=> s!= null).Count();
                                                            }
                                                        }
                                                    }

                                                    Achievement = Achievement / (jmlData == 0 ? 1 : jmlData);

                                                    range = "J" + idx.ToString() + ":J" + idx.ToString();
                                                    SheetRange = ws.get_Range(range, Miss);
                                                    SheetRange.Interior.Color = Color.White;
                                                    SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                                                    SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                                                    SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                                                    SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                                                    SheetRange.Font.Bold = false;
                                                    SheetRange.set_Value(Miss, Achievement.ToString("0.00"));
                                                    TotalRealization = TotalRealization + Achievement;

                                                    range = "K" + idx.ToString() + ":K" + idx.ToString();
                                                    SheetRange = ws.get_Range(range, Miss);
                                                    SheetRange.Interior.Color = Color.White;
                                                    SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                                                    SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                                                    SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                                                    SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                                                    SheetRange.Font.Bold = false;
                                                    SheetRange.set_Value(Miss, Notes);

                                                    range = "L" + idx.ToString() + ":L" + idx.ToString();
                                                    SheetRange = ws.get_Range(range, Miss);
                                                    SheetRange.Interior.Color = Color.White;
                                                    SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                                                    SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                                                    SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                                                    SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                                                    SheetRange.Font.Bold = false;
                                                    SheetRange.set_Value(Miss, ((Achievement * e.KPI.Weight) / 100).ToString("0.00"));
                                                    TotalFinalScore = TotalFinalScore + ((Achievement * e.KPI.Weight) / 100);
                                                }
                                                Numbering++;
                                                idx++;
                                            }
                                        }
                                        else
                                            idx++;
                                    }
                                    //total
                                    range = "H" + idx.ToString() + ":H" + idx.ToString();
                                    SheetRange = ws.get_Range(range, Miss);
                                    SheetRange.Interior.Color = Color.White;
                                    SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                                    SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                                    SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                                    SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                                    SheetRange.Font.Bold = true;
                                    SheetRange.set_Value(Miss, "TOTAL");

                                    range = "I" + idx.ToString() + ":I" + idx.ToString();
                                    SheetRange = ws.get_Range(range, Miss);
                                    SheetRange.Interior.Color = Color.White;
                                    SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                                    SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                                    SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                                    SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                                    SheetRange.Font.Bold = true;
                                    SheetRange.set_Value(Miss, TotalWeight.ToString("0.00"));

                                    range = "J" + idx.ToString() + ":J" + idx.ToString();
                                    SheetRange = ws.get_Range(range, Miss);
                                    SheetRange.Interior.Color = Color.White;
                                    SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                                    SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                                    SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                                    SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                                    SheetRange.Font.Bold = true;
                                    SheetRange.set_Value(Miss, TotalRealization.ToString("0.00"));

                                    range = "K" + idx.ToString() + ":K" + idx.ToString();
                                    SheetRange = ws.get_Range(range, Miss);
                                    SheetRange.Interior.Color = Color.White;
                                    SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                                    SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                                    SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                                    SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                                    SheetRange.Font.Bold = true;
                                    SheetRange.set_Value(Miss, "");

                                    range = "L" + idx.ToString() + ":L" + idx.ToString();
                                    SheetRange = ws.get_Range(range, Miss);
                                    SheetRange.Interior.Color = Color.White;
                                    SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                                    SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                                    SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                                    SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                                    SheetRange.Font.Bold = true;
                                    SheetRange.set_Value(Miss, TotalFinalScore.ToString("0.00"));

                                    GrandTotal = GrandTotal + TotalFinalScore;

                                    idx++;
                                }
                            }
                        }
                    }
                }
                #endregion
                
                #region Qualitative
                else if (a.Description.Trim().ToLower().Contains("qualitative"))
                {
                    range = "A" + idx.ToString() + ":C" + (idx + 1).ToString();
                    SheetRange = ws.get_Range(range, Miss);
                    SheetRange.MergeCells = true;
                    SheetRange.Interior.Color = Color.Yellow;
                    SheetRange.Font.Bold = true;
                    SheetRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    SheetRange.VerticalAlignment = XlVAlign.xlVAlignCenter;
                    SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                    SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                    SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                    SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                    SheetRange.set_Value(Miss, "Values Indicator");

                    range = "D" + idx.ToString() + ":H" + (idx + 1).ToString();
                    SheetRange = ws.get_Range(range, Miss);
                    SheetRange.MergeCells = true;
                    SheetRange.Interior.Color = Color.Yellow;
                    SheetRange.Font.Bold = true;
                    SheetRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    SheetRange.VerticalAlignment = XlVAlign.xlVAlignCenter;
                    SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                    SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                    SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                    SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                    SheetRange.set_Value(Miss, "360 Degrees Performance Feedback");

                    range = "I" + idx.ToString() + ":I" + (idx + 1).ToString();
                    SheetRange = ws.get_Range(range, Miss);
                    SheetRange.MergeCells = true;
                    SheetRange.Interior.Color = Color.Yellow;
                    SheetRange.Font.Bold = true;
                    SheetRange.WrapText = true;
                    SheetRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    SheetRange.VerticalAlignment = XlVAlign.xlVAlignCenter;
                    SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                    SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                    SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                    SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                    SheetRange.set_Value(Miss, "1st Semester");

                    range = "J" + idx.ToString() + ":J" + (idx + 1).ToString();
                    SheetRange = ws.get_Range(range, Miss);
                    SheetRange.MergeCells = true;
                    SheetRange.Interior.Color = Color.Yellow;
                    SheetRange.Font.Bold = true;
                    SheetRange.WrapText = true;
                    SheetRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    SheetRange.VerticalAlignment = XlVAlign.xlVAlignCenter;
                    SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                    SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                    SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                    SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                    SheetRange.set_Value(Miss, "2nd Semester");

                    range = "K" + idx.ToString() + ":K" + (idx + 1).ToString();
                    SheetRange = ws.get_Range(range, Miss);
                    SheetRange.MergeCells = true;
                    SheetRange.Interior.Color = Color.Yellow;
                    SheetRange.Font.Bold = true;
                    SheetRange.WrapText = true;
                    SheetRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    SheetRange.VerticalAlignment = XlVAlign.xlVAlignCenter;
                    SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                    SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                    SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                    SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                    SheetRange.set_Value(Miss, "Final Score");

                    idx = idx + 2;

                    for (int i = 0; i < 3; i++)
                    {
                        range = "A" + idx.ToString() + ":C" + idx.ToString();
                        SheetRange = ws.get_Range(range, Miss);
                        SheetRange.MergeCells = true;
                        SheetRange.Interior.Color = Color.White;
                        SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                        SheetRange.set_Value(Miss, string.Empty);

                        range = "D" + idx.ToString() + ":H" + idx.ToString();
                        SheetRange = ws.get_Range(range, Miss);
                        SheetRange.MergeCells = true;
                        SheetRange.Interior.Color = Color.White;
                        SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                        SheetRange.set_Value(Miss, string.Empty);

                        range = "I" + idx.ToString() + ":I" + idx.ToString();
                        SheetRange = ws.get_Range(range, Miss);
                        SheetRange.MergeCells = true;
                        SheetRange.Interior.Color = Color.White;
                        SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                        SheetRange.set_Value(Miss, string.Empty);

                        range = "J" + idx.ToString() + ":J" + idx.ToString();
                        SheetRange = ws.get_Range(range, Miss);
                        SheetRange.MergeCells = true;
                        SheetRange.Interior.Color = Color.White;
                        SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                        SheetRange.set_Value(Miss, string.Empty);

                        range = "K" + idx.ToString() + ":K" + idx.ToString();
                        SheetRange = ws.get_Range(range, Miss);
                        SheetRange.MergeCells = true;
                        SheetRange.Interior.Color = Color.White;
                        SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                        SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                        SheetRange.set_Value(Miss, string.Empty);
                        
                        idx++;
                    }
                }
                #endregion

                if (a.Description.Trim().ToLower().Contains("quantitative"))
                {
                    range = "H" + idx.ToString() + ":K" + idx.ToString();
                    SheetRange = ws.get_Range(range, Miss);
                    SheetRange.Interior.Color = Color.White;
                    SheetRange.MergeCells = true;
                    SheetRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                    SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                    SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                    SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                    SheetRange.Font.Bold = true;
                    SheetRange.set_Value(Miss, "Score Achieved");

                    range = "L" + idx.ToString() + ":L" + idx.ToString();
                    SheetRange = ws.get_Range(range, Miss);
                    SheetRange.Interior.Color = Color.White;
                    SheetRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                    SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                    SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                    SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                    SheetRange.Font.Bold = true;
                    SheetRange.set_Value(Miss, GrandTotal.ToString("0.00"));
                }
                else if (a.Description.Trim().ToLower().Contains("qualitative"))
                {
                    range = "H" + idx.ToString() + ":J" + idx.ToString();
                    SheetRange = ws.get_Range(range, Miss);
                    SheetRange.Interior.Color = Color.White;
                    SheetRange.MergeCells = true;
                    SheetRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                    SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                    SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                    SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                    SheetRange.Font.Bold = true;
                    SheetRange.set_Value(Miss, "Score Achieved");

                    range = "K" + idx.ToString() + ":K" + idx.ToString();
                    SheetRange = ws.get_Range(range, Miss);
                    SheetRange.Interior.Color = Color.White;
                    SheetRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
                    SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
                    SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
                    SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
                    SheetRange.Font.Bold = true;
                    SheetRange.set_Value(Miss, GrandTotal.ToString("0.00"));
                }

                idx = idx + 2;
            }

            range = "A" + idx.ToString() + ":A" + idx.ToString();
            SheetRange = ws.get_Range(range, Miss);
            SheetRange.Interior.Color = Color.White;
            SheetRange.set_Value(Miss, "I have understood and committed to my above Key Goals");

            range = "F" + idx.ToString() + ":H" + idx.ToString();
            SheetRange = ws.get_Range(range, Miss);
            SheetRange.MergeCells = true;
            SheetRange.Interior.Color = Color.White;
            SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
            SheetRange.set_Value(Miss, "A. Score Quantitative");

            range = "I" + idx.ToString() + ":I" + idx.ToString();
            SheetRange = ws.get_Range(range, Miss);
            SheetRange.MergeCells = true;
            SheetRange.Interior.Color = Color.White;
            SheetRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
            SheetRange.set_Value(Miss, "0.00%");

            range = "J" + idx.ToString() + ":J" + idx.ToString();
            SheetRange = ws.get_Range(range, Miss);
            SheetRange.MergeCells = true;
            SheetRange.Interior.Color = Color.White;
            SheetRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
            SheetRange.set_Value(Miss, "50.00%");

            range = "K" + idx.ToString() + ":K" + idx.ToString();
            SheetRange = ws.get_Range(range, Miss);
            SheetRange.MergeCells = true;
            SheetRange.Interior.Color = Color.White;
            SheetRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
            SheetRange.set_Value(Miss, "0.00%");

            idx++;

            range = "A" + idx.ToString() + ":A" + idx.ToString();
            SheetRange = ws.get_Range(range, Miss);
            SheetRange.Interior.Color = Color.White;
            SheetRange.set_Value(Miss, "Employee's Signature");

            range = "F" + idx.ToString() + ":H" + idx.ToString();
            SheetRange = ws.get_Range(range, Miss);
            SheetRange.MergeCells = true;
            SheetRange.Interior.Color = Color.White;
            SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
            SheetRange.set_Value(Miss, "B. Directorat");

            range = "I" + idx.ToString() + ":I" + idx.ToString();
            SheetRange = ws.get_Range(range, Miss);
            SheetRange.MergeCells = true;
            SheetRange.Interior.Color = Color.White;
            SheetRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
            SheetRange.set_Value(Miss, "0.00%");

            range = "J" + idx.ToString() + ":J" + idx.ToString();
            SheetRange = ws.get_Range(range, Miss);
            SheetRange.MergeCells = true;
            SheetRange.Interior.Color = Color.White;
            SheetRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
            SheetRange.set_Value(Miss, "30.00%");

            range = "K" + idx.ToString() + ":K" + idx.ToString();
            SheetRange = ws.get_Range(range, Miss);
            SheetRange.MergeCells = true;
            SheetRange.Interior.Color = Color.White;
            SheetRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
            SheetRange.set_Value(Miss, "0.00%");

            idx++;

            range = "F" + idx.ToString() + ":H" + idx.ToString();
            SheetRange = ws.get_Range(range, Miss);
            SheetRange.MergeCells = true;
            SheetRange.Interior.Color = Color.White;
            SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
            SheetRange.set_Value(Miss, "C. CA");

            range = "I" + idx.ToString() + ":I" + idx.ToString();
            SheetRange = ws.get_Range(range, Miss);
            SheetRange.MergeCells = true;
            SheetRange.Interior.Color = Color.White;
            SheetRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
            SheetRange.set_Value(Miss, "0.00%");

            range = "J" + idx.ToString() + ":J" + idx.ToString();
            SheetRange = ws.get_Range(range, Miss);
            SheetRange.MergeCells = true;
            SheetRange.Interior.Color = Color.White;
            SheetRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
            SheetRange.set_Value(Miss, "20.00%");

            range = "K" + idx.ToString() + ":K" + idx.ToString();
            SheetRange = ws.get_Range(range, Miss);
            SheetRange.MergeCells = true;
            SheetRange.Interior.Color = Color.White;
            SheetRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
            SheetRange.set_Value(Miss, "0.00%");

            idx++;

            range = "A" + idx.ToString() + ":A" + idx.ToString();
            SheetRange = ws.get_Range(range, Miss);
            SheetRange.Interior.Color = Color.White;
            SheetRange.Font.Underline = true;
            SheetRange.set_Value(Miss, "                                  ");

            range = "H" + idx.ToString() + ":J" + idx.ToString();
            SheetRange = ws.get_Range(range, Miss);
            SheetRange.MergeCells = true;
            SheetRange.Interior.Color = Color.White;
            SheetRange.Font.Bold = true;
            SheetRange.HorizontalAlignment = XlHAlign.xlHAlignLeft;
            SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
            SheetRange.set_Value(Miss, "Grand Total Score");

            range = "K" + idx.ToString() + ":K" + idx.ToString();
            SheetRange = ws.get_Range(range, Miss);
            SheetRange.MergeCells = true;
            SheetRange.Interior.Color = Color.White;
            SheetRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            SheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.Black;
            SheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.Black;
            SheetRange.set_Value(Miss, "0.00%");

            idx++;

            range = "A" + idx.ToString() + ":A" + idx.ToString();
            SheetRange = ws.get_Range(range, Miss);
            SheetRange.Interior.Color = Color.White;
            SheetRange.Font.Underline = true;
            SheetRange.set_Value(Miss, user.FirstName + " " + user.LastName);

            idx++;

            range = "A" + idx.ToString() + ":A" + idx.ToString();
            SheetRange = ws.get_Range(range, Miss);
            SheetRange.Interior.Color = Color.White;
            SheetRange.Font.Underline = true;
            SheetRange.set_Value(Miss, "We are agreed above goals");

            range = "D" + idx.ToString() + ":E" + idx.ToString();
            SheetRange = ws.get_Range(range, Miss);
            SheetRange.Interior.Color = Color.White;
            SheetRange.Font.Underline = true;
            SheetRange.MergeCells = true;
            SheetRange.set_Value(Miss, "Approved,");

            range = "J" + idx.ToString() + ":K" + idx.ToString();
            SheetRange = ws.get_Range(range, Miss);
            SheetRange.Interior.Color = Color.White;
            SheetRange.Font.Underline = true;
            SheetRange.MergeCells = true;
            SheetRange.set_Value(Miss, "Acknowledged by,");

            idx = idx + 2;

            range = "A" + idx.ToString() + ":A" + idx.ToString();
            SheetRange = ws.get_Range(range, Miss);
            SheetRange.Interior.Color = Color.White;
            SheetRange.Font.Underline = true;
            SheetRange.set_Value(Miss, "                                  ");

            range = "D" + idx.ToString() + ":E" + idx.ToString();
            SheetRange = ws.get_Range(range, Miss);
            SheetRange.Interior.Color = Color.White;
            SheetRange.Font.Underline = true;
            SheetRange.MergeCells = true;
            SheetRange.set_Value(Miss, "                                  ");

            range = "J" + idx.ToString() + ":K" + idx.ToString();
            SheetRange = ws.get_Range(range, Miss);
            SheetRange.Interior.Color = Color.White;
            SheetRange.Font.Underline = true;
            SheetRange.MergeCells = true;
            SheetRange.set_Value(Miss, "                                  ");

            idx = idx + 4;

            range = "A" + idx.ToString() + ":A" + idx.ToString();
            SheetRange = ws.get_Range(range, Miss);
            SheetRange.Interior.Color = Color.White;
            SheetRange.Font.Underline = true;
            SheetRange.set_Value(Miss, "Signed at end of year review,");

            range = "D" + idx.ToString() + ":E" + idx.ToString();
            SheetRange = ws.get_Range(range, Miss);
            SheetRange.Interior.Color = Color.White;
            SheetRange.Font.Underline = true;
            SheetRange.MergeCells = true;
            SheetRange.set_Value(Miss, "Approved,");

            range = "J" + idx.ToString() + ":K" + idx.ToString();
            SheetRange = ws.get_Range(range, Miss);
            SheetRange.Interior.Color = Color.White;
            SheetRange.Font.Underline = true;
            SheetRange.MergeCells = true;
            SheetRange.set_Value(Miss, "Acknowledged by,");

            idx = idx + 2;

            range = "A" + idx.ToString() + ":A" + idx.ToString();
            SheetRange = ws.get_Range(range, Miss);
            SheetRange.Interior.Color = Color.White;
            SheetRange.Font.Underline = true;
            SheetRange.set_Value(Miss, "                                  ");

            range = "D" + idx.ToString() + ":E" + idx.ToString();
            SheetRange = ws.get_Range(range, Miss);
            SheetRange.Interior.Color = Color.White;
            SheetRange.Font.Underline = true;
            SheetRange.MergeCells = true;
            SheetRange.set_Value(Miss, "                                  ");

            range = "J" + idx.ToString() + ":K" + idx.ToString();
            SheetRange = ws.get_Range(range, Miss);
            SheetRange.Interior.Color = Color.White;
            SheetRange.Font.Underline = true;
            SheetRange.MergeCells = true;
            SheetRange.set_Value(Miss, "                                  ");

            wb.SaveAs(serveroutputpath, Miss, Miss, Miss, Miss, Miss, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, Miss, Miss, Miss, Miss);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(SheetRange);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
            wb.Close(Miss, Miss, Miss);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
            apps.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(apps);
 
            return outputpath;
        }
    }
}